package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.ReapDetail;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ReapDetailDao extends BaseJpaDao<ReapDetail> {

    @Query(value = "SELECT COUNT(reap_number) FROM t_plant_reap_detail WHERE company_id = ?1", nativeQuery = true)
    String reapNumCount(String companyId);

    @Query(value = "SELECT reap_name, reap_part FROM t_plant_reap_detail WHERE reap_id = ?1 LIMIT 1", nativeQuery = true)
    JSONObject getReapNameByReapId(String reapId);

    @Query(value = "SELECT IFNULL(SUM(tprd.reap_number), 0) totalNum FROM t_plant_reap_detail tprd JOIN t_plant_reap tpr ON tprd.reap_id = tpr.id WHERE " +
            "DATE_FORMAT(tpr.begin_time, '%Y-%m') = ?1 AND tpr.company_id = ?2", nativeQuery = true)
    Float getTotalNum(String time, String companyId);

    @Query(value = "SELECT IFNULL(SUM(tprd.reap_number), 0) totalNum FROM t_plant_reap_detail tprd JOIN t_plant_reap tpr ON tprd.reap_id = tpr.id WHERE " +
            "DATE_FORMAT(tpr.begin_time, '%Y-%m') = ?1 AND tpr.company_id = ?2 AND tpr.jy_name = ?3", nativeQuery = true)
    Float getTotalNum(String time, String companyId, String jyName);


    @Query(value = "SELECT jy_name jyName, total, `num`, CONCAT(round(`num` / total * 100.00, 3), '%') percent FROM " +
            "( SELECT * FROM (SELECT tpr.jy_name, SUM(tprd.reap_number) `num` FROM t_plant_reap_detail tprd JOIN t_plant_reap tpr ON tprd.reap_id = tpr.id " +
            "WHERE tpr.company_id = ?1 AND tpr.plant_time BETWEEN ?2 AND ?3 GROUP BY tpr.jy_name ) t1 JOIN " +
            "(SELECT SUM(tprd.reap_number) total FROM t_plant_reap_detail tprd JOIN t_plant_reap tpr ON tprd.reap_id = tpr.id  WHERE tpr.company_id = ?1 AND tpr.plant_time BETWEEN ?2 AND ?3) " +
            "t2) t", nativeQuery = true)
    List<JSONObject> getPercent(String companyId, String startTime, String endTime);


    ReapDetail findById(String id);

    List<ReapDetail> findByReapId(String reapId);

    ReapDetail getByReapId(String reapId);

    @Query(value = "select reap_name as reapName from t_plant_reap_detail group by reap_name", nativeQuery = true)
    List<String> findAllBreed();

    @Query(value = "select reap_name from t_plant_reap_detail where company_id = ?1 group by reap_name", nativeQuery = true)
    List<String> findSelect(String companyId);

    // 根据采收ID集合获取对应的详情信息
    @Query(value = "SELECT tprd.* FROM t_plant_reap tpr JOIN t_plant_reap_detail tprd ON tpr.id = tprd.reap_id WHERE tpr.id IN (?1)", nativeQuery = true)
    List<ReapDetail> getDetailByReapIds(List<String> ids);

}
